<!DOCTYPE HTML>
<!--
	Dimension by HTML5 UP
	html5up.net | @ajlkn
	Free for personal and commercial use under the CCA 3.0 license (html5up.net/license)
-->
<html>
 <head>
  <title>
   Dimension by HTML5 UP
  </title>
  <!-- <meta charset="utf-8" /> -->
  <!-- <meta name="viewport" content="width=device-width, initial-scale=1, user-scalable=no" /> -->
  <meta charset="utf-8"/>
  <meta content="width=device-width,initial-scale=1.0" name="viewport"/>
  <link href="../../assets/css/article.css" rel="stylesheet"/>
  <link href="https://cdn.bootcss.com/highlight.js/9.15.8/styles/github.min.css" rel="stylesheet"/>
  <noscript>
   <link href="../../assets/css/noscript.css" rel="stylesheet"/>
  </noscript>
 </head>
 <body>
  <div id="app">
  </div>
  <!-- built files will be auto injected -->
 </body>
 <body class="is-preload">
  <!-- Wrapper -->
  <div id="wrapper">
   <!-- Main -->
   <div id="main">
    <article id="article">
     <h1 id="mysql-docker">
      Mysql Docker 主从配置
     </h1>
     <hr/>
     <p>
      <em>
       操作系统：Windows10 使用的MySQL8.0的docker镜像
      </em>
     </p>
     <h2 id="_1">
      总览
     </h2>
     <h3 id="_2">
      前置环境说明
     </h3>
     <p>
      首先在系统中起了一个MySQL8.0的docker镜像，并在其中插入了初始数据，容器名就叫mysql（视为主库），从库在主从分离前还没有启动
     </p>
     <p>
      操作日志如下：
     </p>
     <div class="codehilite">
      <pre><span></span><code><span class="c1"># 拉取MySQL8.0版本镜像并启动，监听在3306端口，设置root用户密码为root</span>
docker run --name mysql -p <span class="m">3306</span>:3306 -e <span class="nv">MYSQL_ROOT_PASSWORD</span><span class="o">=</span>root -e <span class="nv">MYSQL_ROOT_HOST</span><span class="o">=</span>% -d mysql:latest
</code></pre>
     </div>
     <p>
      其中使用了脚本，自动生成了表和插入数据，下面有脚本链接，可进行点击查看：
     </p>
     <ul>
      <li>
       <a href="https://github.com/lw1243925457/JAVA-000/blob/main/Week_06/init.sql">
        数据库表初始化脚本
       </a>
      </li>
      <li>
       <a href="https://github.com/lw1243925457/JAVA-000/blob/main/Week_06/fillData1.0.sql">
        数据库数据插入初始化脚本
       </a>
      </li>
     </ul>
     <h3 id="_3">
      数据导入导出
     </h3>
     <p>
      这里不使用脚本对从库进行初始化，而是从主库中导出数据，导入到从库中
     </p>
     <p>
      注意：如果主库有在使用，需要停掉相关的使用程序，或者对主库进行加锁
     </p>
     <p>
      操作日志如下：
     </p>
     <div class="codehilite">
      <pre><span></span><code><span class="c1"># 首先从主库中备份数据到本地（在sh中提前进入准备好的目录）</span>
docker <span class="nb">exec</span> mysql /usr/bin/mysqldump -u root --password<span class="o">=</span>root <span class="nb">test</span> &gt; backup.sql

<span class="c1"># 运行一个新的mysql，作为从库</span>
docker run --name mysql_bk1 -p <span class="m">3309</span>:3306 -e <span class="nv">MYSQL_ROOT_PASSWORD</span><span class="o">=</span>root -e <span class="nv">MYSQL_ROOT_HOST</span><span class="o">=</span>% -d mysql:latest

<span class="c1"># 新开的数据启动可能需要点时间，请耐心等候，使用下面的命令可以看到mysql的运行日志，运行到新的日志内容就说明准备就绪了，可以进行使用</span>
docker logs -f mysql_bk1
<span class="c1"># /usr/sbin/mysqld: ready for connections. Version: '8.0.22'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  MySQL Community Server - GPL.</span>

<span class="c1"># 进入到新开的数据库中，建立数据库test</span>
docker <span class="nb">exec</span> -ti mysql_bk1 mysql -u root -p
create database test<span class="p">;</span>

<span class="c1"># Ctrl-D退出，执行下面的命令导入数据到新开的mysql中，这个需要花费好几分钟，请耐心等待；当然也可以docker cp backup.sql文件到容器中，然后连上数据库使用source backup.sql</span>
cat backup.sql <span class="p">|</span> docker <span class="nb">exec</span> -i mysql_bk1 /usr/bin/mysql -u root --password<span class="o">=</span>root <span class="nb">test</span>
</code></pre>
     </div>
     <h3 id="_4">
      主库设置
     </h3>
     <p>
      首先在本地编写配置文件，这里使用的配置文件如下，根据情况进行修改即可：
     </p>
     <div class="codehilite">
      <pre><span></span><code><span class="c1"># Copyright (c) 2017, Oracle and/or its affiliates. All rights reserved.</span>
<span class="c1">#</span>
<span class="c1"># This program is free software; you can redistribute it and/or modify</span>
<span class="c1"># it under the terms of the GNU General Public License as published by</span>
<span class="c1"># the Free Software Foundation; version 2 of the License.</span>
<span class="c1">#</span>
<span class="c1"># This program is distributed in the hope that it will be useful,</span>
<span class="c1"># but WITHOUT ANY WARRANTY; without even the implied warranty of</span>
<span class="c1"># MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the</span>
<span class="c1"># GNU General Public License for more details.</span>
<span class="c1">#</span>
<span class="c1"># You should have received a copy of the GNU General Public License</span>
<span class="c1"># along with this program; if not, write to the Free Software</span>
<span class="c1"># Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA  02110-1301 USA</span>

<span class="c1">#</span>
<span class="c1"># The MySQL  Server configuration file.</span>
<span class="c1">#</span>
<span class="c1"># For explanations see</span>
<span class="c1"># http://dev.mysql.com/doc/mysql/en/server-system-variables.html</span>

<span class="o">[</span>mysqld<span class="o">]</span>
pid-file        <span class="o">=</span> /var/run/mysqld/mysqld.pid
<span class="nv">socket</span>          <span class="o">=</span> /var/run/mysqld/mysqld.sock
<span class="nv">datadir</span>         <span class="o">=</span> /var/lib/mysql
secure-file-priv<span class="o">=</span> NULL

<span class="c1"># Custom config should go here</span>
!includedir /etc/mysql/conf.d/

<span class="c1">#主从设置</span>
<span class="c1">#主数据库端ID号</span>
<span class="nv">server_id</span> <span class="o">=</span> <span class="m">1</span>           
 <span class="c1">#开启二进制日志                  </span>
log-bin <span class="o">=</span> mysql-bin    
<span class="c1">#需要复制的数据库名，如果复制多个数据库，重复设置这个选项即可                  </span>
binlog-do-db <span class="o">=</span> <span class="nb">test</span>  
<span class="c1">#将从服务器从主服务器收到的更新记入到从服务器自己的二进制日志文件中                 </span>
log-slave-updates                        
<span class="c1">#控制binlog的写入频率。每执行多少次事务写入一次(这个参数性能消耗很大，但可减小MySQL崩溃造成的损失) </span>
<span class="nv">sync_binlog</span> <span class="o">=</span> <span class="m">1</span>                    
<span class="c1">#这个参数一般用在主主同步中，用来错开自增值, 防止键值冲突</span>
<span class="nv">auto_increment_offset</span> <span class="o">=</span> <span class="m">1</span>           
<span class="c1">#这个参数一般用在主主同步中，用来错开自增值, 防止键值冲突</span>
<span class="nv">auto_increment_increment</span> <span class="o">=</span> <span class="m">1</span>            
<span class="c1">#二进制日志自动删除的天数，默认值为0,表示“没有自动删除”，启动时和二进制日志循环时可能删除  </span>
<span class="nv">expire_logs_days</span> <span class="o">=</span> <span class="m">7</span>                    
<span class="c1">#将函数复制到slave  </span>
<span class="nv">log_bin_trust_function_creators</span> <span class="o">=</span> <span class="m">1</span>    
</code></pre>
     </div>
     <p>
      操作日志如下：
     </p>
     <div class="codehilite">
      <pre><span></span><code><span class="c1"># 将宿主机本地的配置文件复制替换MySQL的配置文件，并重启mysql</span>
docker cp .<span class="se">\m</span>ysql_master.conf mysql:/etc/mysql/my.cnf
docker restart mysql

<span class="c1"># 进入mysql中，查看master的状态，注意下面的File和Position，在从库设置中需要使用</span>
docker <span class="nb">exec</span> -ti mysql mysql -u root -p
show master status<span class="se">\G</span><span class="p">;</span>

*************************** <span class="m">1</span>. row ***************************
             File: mysql-bin.000001
         Position: <span class="m">156</span>
     Binlog_Do_DB: <span class="nb">test</span>
 Binlog_Ignore_DB:
Executed_Gtid_Set:
<span class="m">1</span> row in <span class="nb">set</span> <span class="o">(</span><span class="m">0</span>.00 sec<span class="o">)</span>
</code></pre>
     </div>
     <h3 id="_5">
      从库设置
     </h3>
     <p>
      首先在本地编写配置文件，这里使用的配置文件链接如下，根据情况进行修改即可：
     </p>
     <div class="codehilite">
      <pre><span></span><code><span class="c1"># Copyright (c) 2017, Oracle and/or its affiliates. All rights reserved.</span>
<span class="c1">#</span>
<span class="c1"># This program is free software; you can redistribute it and/or modify</span>
<span class="c1"># it under the terms of the GNU General Public License as published by</span>
<span class="c1"># the Free Software Foundation; version 2 of the License.</span>
<span class="c1">#</span>
<span class="c1"># This program is distributed in the hope that it will be useful,</span>
<span class="c1"># but WITHOUT ANY WARRANTY; without even the implied warranty of</span>
<span class="c1"># MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the</span>
<span class="c1"># GNU General Public License for more details.</span>
<span class="c1">#</span>
<span class="c1"># You should have received a copy of the GNU General Public License</span>
<span class="c1"># along with this program; if not, write to the Free Software</span>
<span class="c1"># Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA  02110-1301 USA</span>

<span class="c1">#</span>
<span class="c1"># The MySQL  Server configuration file.</span>
<span class="c1">#</span>
<span class="c1"># For explanations see</span>
<span class="c1"># http://dev.mysql.com/doc/mysql/en/server-system-variables.html</span>

<span class="o">[</span>mysqld<span class="o">]</span>
pid-file        <span class="o">=</span> /var/run/mysqld/mysqld.pid
<span class="nv">socket</span>          <span class="o">=</span> /var/run/mysqld/mysqld.sock
<span class="nv">datadir</span>         <span class="o">=</span> /var/lib/mysql
secure-file-priv<span class="o">=</span> NULL

<span class="c1"># Custom config should go here</span>
!includedir /etc/mysql/conf.d/

<span class="c1"># 主从设置</span>
<span class="c1"># 从数据库端ID号</span>
<span class="nv">server_id</span> <span class="o">=</span> <span class="m">2</span>           
<span class="c1"># 开启二进制日志                  </span>
log-bin <span class="o">=</span> mysql-bin    
<span class="c1"># 需要复制的数据库名，如果复制多个数据库，重复设置这个选项即可                  </span>
binlog-do-db <span class="o">=</span> <span class="nb">test</span>  
<span class="c1"># 将从服务器从主服务器收到的更新记入到从服务器自己的二进制日志文件中                 </span>
log-slave-updates                        
<span class="c1"># 控制binlog的写入频率。每执行多少次事务写入一次(这个参数性能消耗很大，但可减小MySQL崩溃造成的损失) </span>
<span class="nv">sync_binlog</span> <span class="o">=</span> <span class="m">0</span>                    
<span class="c1">#log buffer将每秒一次地写入log file中，并且log file的flush(刷到磁盘)操作同时进行。该模式下在事务提交的时候，不会主动触发写入磁盘的操作</span>
<span class="nv">innodb_flush_log_at_trx_commit</span> <span class="o">=</span> <span class="m">0</span>
<span class="c1"># MySQL主从复制的时候，当Master和Slave之间的网络中断，但是Master和Slave无法察觉的情况下（比如防火墙或者路由问题）。</span>
<span class="c1"># Slave会等待slave_net_timeout设置的秒数后，才能认为网络出现故障，然后才会重连并且追赶这段时间主库的数据</span>
slave-net-timeout <span class="o">=</span> <span class="m">60</span>                    
<span class="nv">log_bin_trust_function_creators</span> <span class="o">=</span> <span class="m">1</span>
</code></pre>
     </div>
     <p>
      操作日志如下：
     </p>
     <div class="codehilite">
      <pre><span></span><code><span class="c1"># 将宿主机本地的配置文件复制替换MySQL的配置文件，并重启mysql</span>
docker cp .<span class="se">\m</span>ysql_slave1.conf mysql_bk1:/etc/mysql/my.cnf
docker restart mysql_bk1

<span class="c1"># 需要安全证书，执行下面的命令获取证书，并进入MySQL汇中</span>
docker <span class="nb">exec</span> -ti mysql_bk1 mysql --ssl-mode<span class="o">=</span>DISABLED -h <span class="m">192</span>.168.101.104 -uroot -proot --get-server-public-key
<span class="c1"># 设置主库链接,master_log_file/master_log_pos设置从上面主库的File和Position，设置完成后启动</span>
<span class="c1"># 这里的IP设置可以使用宿主机IP，也可以使用docker容器的ip（docker inspect [容器id]|grep IPA 查看 IP）</span>
<span class="c1"># MASTER_HOST：主数据库的主机ip</span>
<span class="c1"># MASTER_PORT：主数据库的端口，不设置则默认是3306</span>
<span class="c1"># MASTER_USER：主数据库被授予同步复制权限的用户名</span>
<span class="c1"># MASTER_PASSWORD：对应的用户密码</span>
<span class="c1"># MASTER_LOG_FILE：在主数据库执行命令show master status 查询到的二进制日志文件名称</span>
<span class="c1"># MASTER_LOG_POS：在主数据库执行命令show master status 查询到的位置 Position的值</span>
change master to <span class="nv">master_host</span><span class="o">=</span><span class="s1">'192.168.101.104'</span>,master_user<span class="o">=</span><span class="s1">'root'</span>,master_password<span class="o">=</span><span class="s1">'root'</span>,master_log_file<span class="o">=</span><span class="s1">'mysql-bin.000001'</span>,master_log_pos<span class="o">=</span><span class="m">156</span><span class="p">;</span>
start slave<span class="p">;</span>

<span class="c1"># 查看，大致如下，没有错误即可</span>
show slave status<span class="se">\G</span><span class="p">;</span>

mysql&gt; show slave status<span class="se">\G</span><span class="p">;</span>
*************************** <span class="m">1</span>. row ***************************
               Slave_IO_State: Waiting <span class="k">for</span> master to send event
                  Master_Host: <span class="m">192</span>.168.101.104
                  Master_User: root
                  Master_Port: <span class="m">3306</span>
                Connect_Retry: <span class="m">60</span>
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: <span class="m">859</span>
               Relay_Log_File: 28bc4fb44f99-relay-bin.000002
                Relay_Log_Pos: <span class="m">324</span>
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: <span class="m">0</span>
                   Last_Error:
                 Skip_Counter: <span class="m">0</span>
          Exec_Master_Log_Pos: <span class="m">859</span>
              Relay_Log_Space: <span class="m">540</span>
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: <span class="m">0</span>
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: <span class="m">0</span>
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: <span class="m">0</span>
                Last_IO_Error:
               Last_SQL_Errno: <span class="m">0</span>
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: <span class="m">1</span>
                  Master_UUID: f6b20c68-23d1-11eb-a497-0242ac110002
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: <span class="m">0</span>
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has <span class="nb">read</span> all relay log<span class="p">;</span> waiting <span class="k">for</span> more updates
           Master_Retry_Count: <span class="m">86400</span>
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: <span class="m">0</span>
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
       Master_public_key_path:
        Get_master_public_key: <span class="m">0</span>
            Network_Namespace:
<span class="m">1</span> row in set, <span class="m">1</span> warning <span class="o">(</span><span class="m">0</span>.01 sec<span class="o">)</span>
</code></pre>
     </div>
     <p>
      另外一个从库，配置和上面的命令完全一样，只是将mysql_bk1换成mysql_bk2即可
     </p>
     <h3 id="_6">
      主从测试
     </h3>
     <p>
      进入到主库中，插入一条数据，在从库中查询可以看到即可
     </p>
     <p>
      操作日志如下：
     </p>
     <div class="codehilite">
      <pre><span></span><code>docker <span class="nb">exec</span> -ti mysql mysql -u root -p
insert into stores <span class="o">(</span>name, description<span class="o">)</span> VALUES <span class="o">(</span><span class="s2">"name103"</span>, <span class="s2">"description103"</span><span class="o">)</span><span class="p">;</span>

docker <span class="nb">exec</span> -ti mysql mysql_bk1 -u root -p
<span class="k">select</span> * from stores<span class="p">;</span>
</code></pre>
     </div>
     <h2 id="_7">
      参考链接
     </h2>
     <ul>
      <li>
       <a href="https://www.cnblogs.com/summertime-wu/p/11637520.html">
        Mysql 主从复制搭建-极简版
       </a>
      </li>
      <li>
       <a href="https://blog.csdn.net/u013068184/article/details/107691389">
        MySQL8.0主从复制的配置
       </a>
      </li>
      <li>
       <a href="https://www.jianshu.com/p/b0cf461451fb">
        MySQL主从配置详解
       </a>
      </li>
     </ul>
    </article>
   </div>
   <!-- Footer -->
   <footer id="footer">
    <p class="copyright">
     © Untitled. Design:
     <a href="https://html5up.net">
      HTML5 UP
     </a>
     .
    </p>
   </footer>
  </div>
  <!-- BG -->
  <div id="bg">
  </div>
  <!-- Scripts -->
  <script src="../assets/js/jquery.min.js">
  </script>
  <script src="../assets/js/browser.min.js">
  </script>
  <script src="../assets/js/breakpoints.min.js">
  </script>
  <script src="../assets/js/util.js">
  </script>
  <script src="../assets/js/main.js">
  </script>
 </body>
</html>
